package com.tr.rentcar.model.dao;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.tr.rentcar.model.GlobalParamHandler;
import com.tr.rentcar.model.bean.CarComment;
import com.tr.rentcar.model.bean.Order;
import com.tr.rentcar.model.bean.User;
import com.tr.rentcar.model.bean.UserJf;
import com.tr.rentcar.model.jdbc.MySqlDb;
import com.tr.rentcar.model.jdbc.ResultHandler;
/**
 * 订单dao
 * @author lenovo
 *
 */
public class OrderDao implements ResultHandler<Order>{
	private MySqlDb db= MySqlDb.getInstance();
	/*
	 * 修改状态
	 */
	public boolean updatestatus(String ostatus,int oid) throws SQLException{
		String sql="UPDATE  `order` set ostatus=?   WHERE oid=?";
		return db.executeUpdate(sql, new GlobalParamHandler(ostatus,oid))>0;
	}
	/**
	 * 订单金额减少
	 */
	public boolean updateomoney(Double omoney,int oid) throws SQLException{
		String sql="UPDATE  `order` set omoney=omoney-?   WHERE oid=?";
		return db.executeUpdate(sql, new GlobalParamHandler(omoney,oid))>0;
	}
	
	/**
	 * 根据订单编号获取订单信息
	 * @param ono
	 * @return
	 * @throws SQLException
	 */
	public Order findByOno(String ono) throws SQLException{
		String sql="select * from order_he where ono=? order by odate desc";
		return db.queryUninResult(sql, new GlobalParamHandler(ono), this);
	}
	/**
	 * 根据订单id获取订单信息
	 * @param ono
	 * @return
	 * @throws SQLException
	 */
	public Order findByOid(int oid) throws SQLException{
		String sql="select * from order_he where oid=? order by odate desc";
		return db.queryUninResult(sql, new GlobalParamHandler(oid), this);
	}
	
	/**
	 * 查询所有字段
	 */
	public List<Order> findAll() throws SQLException{
		String sql="select * from order_he";
		return db.executeQuery(sql, this);
	}
	/**
	 * 根据用户id查询
	 */
	public List<Order> findById(int uid) throws SQLException {
		String sql = "select * from order_he where uid = ? order by odate desc";
		return db.executeQuery(sql, new GlobalParamHandler(uid), this);
	}
	/**
	 * 通过用户ID查询记录数
	 */
	public long getAllCountById(int uid) throws SQLException{
		String sql="select count(*)as count from order_he where uid=?";
		return (long) db.executeCountQuery(sql,new GlobalParamHandler(uid));
	}
	/**
	 * 通过用户ID和状态查询记录数
	 */
	public long getAllCountByIdState(int uid,String state) throws SQLException{
		String sql="select count(*)as count from order_he where uid=? and ostatus=?";
		return (long) db.executeCountQuery(sql,new GlobalParamHandler(uid,state));
	}
	/**
	 * 通过id和订单状态查询查寻
	 */
	public List<Order> getByIdState(int uid,String ostatus,int c) throws SQLException {
		StringBuffer sql=new StringBuffer("select * from order_he where uid = "+uid+" and ostatus='"+ostatus+"' order by odate desc");
		sql.append(" limit "+c+",9");
		return db.executeQuery(sql.toString(), this);
	}
	/**
	 * 通过id和订单状态查询查寻
	 */
	public List<Order> getById(int uid,int c) throws SQLException {
		StringBuffer sql=new StringBuffer("select * from order_he where uid = "+uid+" order by odate desc");
		sql.append(" limit "+c+",9");
		return db.executeQuery(sql.toString(), this);
	}
	/**
	 * 根据订单id查询
	 */
	public Order findByoId(int oId) throws SQLException {
		String sql = "select * from order_he where  oId= ?";
		return db.queryUninResult(sql, new GlobalParamHandler(oId),this);
	}
	/**
	 * 根据用户id和订单状态查询
	 * @param uaTel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findByIdState(int uid,String ostatus) throws SQLException {
		String sql = "select * from order_he where uid = ? and ostatus=? order by odate desc";
		return db.executeQuery(sql, new GlobalParamHandler(uid,ostatus), this);
	}
	/**
	 * 只根据状态查找
	 * @param uaTel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findByState(String ostatus) throws SQLException {
		String sql = "select * from order_he where ostatus=? ";
		return db.executeQuery(sql, new GlobalParamHandler(ostatus), this);
	}
	/**
	 * 通过订单状态查询记录数
	 */
	public long getAllCountByState(String state) throws SQLException{
		String sql="select count(*)as count from order_he where ostatus=?";
		return (long) db.executeCountQuery(sql,new GlobalParamHandler(state));
	}
	/**
	 * 获取车辆品牌销量
	 * @throws SQLException 
	 */
	public long getBrandCount(String name) throws SQLException{
		String sql="SELECT (COUNT(*)) FROM car_brand_cars_order where cb_name='"+name+"'";
		return (long) db.executeCountQuery(sql);
	}
	
	
	/**
	 * 统计按月，按日，按年的收入
	 * @throws SQLException 
	 */
	public Double getMoney(String time) throws SQLException{
		String sql="select SUM(omoney) from `order` where ostatus='已完成' and odate like '"+time+"%' ";
		return (Double) db.executeCountQuery(sql);
	}
	
	/**
	 * 查询除了未支付外所有的订单,并按状态排序
	 * @param uaTel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findAllExcNopay(int pageStart,int pageSize)throws SQLException{
		String sql="select * from order_he where ostatus!='未支付' order by ostatus,take_time limit "+(pageStart-1)*pageSize+","+pageSize;
		return db.executeQuery(sql,  this);
	}
	/**
	 * 查询所有未处理的预订单
	 * @param uaTel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findallpre(int pageStart,int pageSize)throws SQLException{
		String sql="select * from order_he where ostatus='未处理' order by take_time limit "+(pageStart-1)*pageSize+","+pageSize;
		return db.executeQuery(sql,  this);
	}
	
	/**
	 *按订单编号或联系人电话模糊查询预订单
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findNopayByNoAndTel(String tel) throws SQLException{
		String sql="select * from order_he where ua_tel like ? or ono like ? ";
		return db.executeQuery(sql, new GlobalParamHandler("%"+tel+"%","%"+tel+"%"), this);
	}
	
	/**
	 *按订单编号或联系人电话模糊查询所有订单
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Order> findByNoAndTel(String tel) throws SQLException{
		String sql="select * from order_he where ua_tel like ? or ono like ?";
		return db.executeQuery(sql, new GlobalParamHandler("%"+tel+"%","%"+tel+"%"), this);
	}
	
	/**
	 * 获取所有订单总数
	 * @return
	 * @throws SQLException
	 */
	public Long getAllCount() throws SQLException{
		String sql="select count(*) from order_he where ostatus!='未支付'";
		return (Long) db.executeCountQuery(sql);
	}
	
	/**
	 * 获取未处理订单总数
	 * @return
	 * @throws SQLException
	 */
	public Long getByNohandleCount() throws SQLException{
		String sql="select count(*) from order_he where ostatus='未处理'";
		return (Long) db.executeCountQuery(sql);
	}
	
	/**
	 * 延期还车修改
	 */
	public boolean yanqi(Double omoney,String date,int oid) throws SQLException{
		String sql="UPDATE  `order` set omoney=omoney+?,return_time=?  WHERE oid=?";
		return db.executeUpdate(sql, new GlobalParamHandler(omoney,date,oid))>0;
	}
	
	/**
	 * 更改订单状态(含司机信息)
	 * @param ostatus
	 * @param ename
	 * @param oid
	 * @return
	 * @throws SQLException
	 */
	public boolean updateStatus(String ostatus,String ename,int oid) throws SQLException{
		String sql="UPDATE  `order` set ostatus=? , ename=?  WHERE oid=?";
		return db.executeUpdate(sql, new GlobalParamHandler(ostatus,ename,oid))>0; 
	}
	
	/**
	 * 删除订单
	 * @param oid
	 * @return
	 * @throws SQLException
	 *//*
	public boolean deleteOrder(int oid) throws SQLException{
		String sql="delete from `order` where oid=?";
		return db.executeUpdate(sql, new GlobalParamHandler(oid))>0;
	}*/
	/**
	 * 插入订单数据
	 * @param ono
	 * @param omoney
	 * @param ocontent
	 * @param cid
	 * @param uaName
	 * @param uaIdentity
	 * @param uid
	 * @param takeTime
	 * @param returnTime
	 * @param takePlace
	 * @param returnPlace
	 * @return
	 * @throws SQLException
	 */
	public boolean insertOrder(String ono,String omoney,String ocontent,String cid,String uaName,String uaIdentity,String uaTel,int uid,Date takeTime,Date returnTime,String takePlace,String returnPlace) throws SQLException{
		String sql="INSERT INTO `order`(ono,omoney,ocontent,cid,ua_name,ua_identity,ua_tel,uid,take_time,return_time,take_place,return_place) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		return db.executeUpdate(sql,new GlobalParamHandler(ono,omoney,ocontent,cid,uaName,uaIdentity,uaTel,uid,takeTime,returnTime,takePlace,returnPlace))>0;
	}
	
	
	
	@Override
	public Order doHander(Map<String, Object> row) {
		Order o=new Order();
		
		o.setcId(Integer.parseInt(row.get("cid")+""));
		o.setcName(row.get("cname")+"");
		o.setoContent(row.get("ocontent")+"");
		o.setoDate((Date)row.get("odate"));
		o.setoId(Integer.parseInt(row.get("oid")+""));
		o.setoMoney(Double.valueOf(row.get("omoney")+""));
		o.setoNo(row.get("ono")+"");
		o.setoRemark(row.get("oremark")+"");
		o.setoStatus(row.get("ostatus")+"");
		o.setUaTel(row.get("ua_tel")+"");
		o.setUaIdentity(row.get("ua_identity")+"");
		o.setUaName(row.get("ua_name")+"");
		o.setuId(Integer.parseInt(row.get("uid")+""));
		o.setTakeTime((Date)row.get("take_time"));
		o.setReturnTime((Date)row.get("return_time"));
		o.setTakePlace(row.get("take_place")+"");
		o.setReturnPlace(row.get("return_place")+"");
		o.setcMoney(Double.valueOf(row.get("cmoney")+""));
		o.setEname(row.get("ename")+"");
		return o;
	}

}
